<?php
    /**
    * Created by PhpStorm.
    * User: JM003
    * Date: 2020/10/16
    * Time: 15:22
    */
    namespace app\admin\controller;
    use app\admin\Common\Getpinyin;
    use app\admin\model\UploadfileModel;
    use think\Db;
    class Importexcel extends Base {
        /**
        * 无表导入excel
        * @return array
        */
        public function getFile() {
            ini_set('memory_limit', '-1');
            require_once (EXTEND_PATH.'/PHPExcel/Classes/PHPExcel.php');
            setlocale(LC_ALL, 'zh_CN.UTF-8'); //解决 pathinfo 中文乱码的问题
            $start = microtime(true); //开启代码运行时间
            $extlist = 'xlsx,xls,csv';
            if (request() ->isAjax()) {
                $file = request() ->file('file');
                $oldFileinfo = $file->getInfo();
                $oldFileNameinfo = $oldFileinfo['name'];
                $fileinfo = pathinfo($oldFileNameinfo);
                $fileExt = $fileinfo['extension'];   //文件扩展
                $basename = $fileinfo['basename'];   //带扩展的文件名
                $oldfilename = $fileinfo['filename']; //不带扩展文件名
                $getpinyin = new Getpinyin();
                $py = $getpinyin->getPY($oldfilename);
                $prefix = config('database.prefix');    //数据库表扩展名
                $excdatatable = $prefix."uploadfiledata_$py"; //创建数据库表名
                $where = "isdelete=0 and filetablename='$excdatatable'";
                $oldData = Db::query("SHOW TABLES LIKE '%$excdatatable%';"); //查询是否存在数据表
                if ($oldData) {
                    //有，则删除老数据表
                    $deltablesql = "drop table $excdatatable";
                    $delresult = Db::execute($deltablesql);
                    if ($delresult === false) {
                        return msg('-1', '', '上传失败：删除原表失败');
                    }
                }
                //echo $oldData;die;
                $info = $file->validate(['ext'=>$extlist]) ->move(ROOT_PATH.'public'.DS.'upload'.'/'.'excle');
                if ($info) {
                    $path = './upload'.'/'.'excle'.'/'.date('Ymd') .'/'.$info->getFilename(); //文件保存路径
                    if (empty($path) OR !file_exists($path)) {
                        //die('file not exists!');
                        return msg('-1', '', '上传失败：file not exists!');
                    }
                    if ($fileExt == 'xlsx') {
                        $type = 'Excel2007';
                    } elseif ($fileExt == 'xls') {
                        $type = 'Excel5';
                    } else {
                        $type = 'CSV';
                    }
                    //echo $fileExt;die;
                    $objReader = \PHPExcel_IOFactory::createReader($type); //判断使用哪种格式
                    if (!$objReader->canRead($path)) {                //判断是否是其他文档修改后缀的excel
                        die ('No Excel!');
                    }
                    $objReader->setReadDataOnly(true); //只读取数据,会智能忽略所有空白行,这点很重要.
                    if ($fileExt == 'xlsx' || $fileExt == 'xls') {
                        $objPHPExcel = $objReader->load($path);
                        //                    $sheetCount = $objPHPExcel->getSheetCount();//获取sheet工作表总个数
                        //
                        //                    if($sheetCount>1){
                        //                        $RowNum = 0;
                        //                        /*读取表格数据*/
                        //                        for($i =0; $i < $sheetCount;$i++){//循环sheet工作表的总个数
                        //                            $sheet = $objPHPExcel->getSheet($i);
                        //                            $highestRow = $sheet->getHighestRow();//每个工作表的总行数
                        //
                        //                            $RowNum += $highestRow-1;//计算所有sheet的总行数
                        //                            $highestColumn = $sheet->getHighestColumn();//取得最大的列号
                        //                            //从第$i个sheet的第1行开始获取数据
                        //                            for($row = 1;$row <= $highestRow;$row++){
                        //                                //把每个sheet作为一个新的数组元素 键名以sheet的索引命名 利于后期数组的提取
                        //
                        //                                $data[$i] = $objPHPExcel->getSheet($i)->toArray();
                        //                            }
                        //                        }
                        //
                        //                        //dump($rowData);die;
                        //                    }else{
                        //                        $data[] = $objPHPExcel->getSheet()->toArray();
                        //                    }
                        $data = $objPHPExcel->getSheet() ->toArray();
                    } elseif ($fileExt == 'csv') {
                        $objReader = $objReader->setDelimiter(',') ->setInputEncoding('GBK') ->setEnclosure('"') ->setLineEnding("\r\n") ->setSheetIndex(0);
                        $objPHPExcel = $objReader->load($path);
                        $data = $objPHPExcel->getSheet() ->toArray();
                    }
                    //array_shift($data);//去掉第一行表头数据
                    if ($data) {
                        $header = $data[0];
                        $headerArray = [];
                        Db::startTrans();
                        $sql = "CREATE TABLE $excdatatable (";
                        $sql.= "id int(11) NOT NULL AUTO_INCREMENT,";
                        foreach($header as $k=>$v) {
                            $field = \PHPExcel_Cell::stringFromColumnIndex($k); //$K 转换成 0->A
                            $Colname[] = \PHPExcel_Cell::stringFromColumnIndex($k); //$K 转换成 0->A
                            $name = trim($v);
                            $sql.= "`$field` varchar(255) DEFAULT NULL COMMENT '$name',";
                            $headerArray[$field] = $name;
                        }
                        // dump($headerArray);die;
                        $sql.= "PRIMARY KEY ( `id` )";
                        $sql.= ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='$oldfilename 表';"; //根据excel表名称创建数据表
                        $result = Db::execute($sql);
                        if ($result !== false) {
                            $inst['filetablename'] = $excdatatable;
                            $inst['filepath'] = $path;
                            $inst['oldfilename'] = $basename;
                            $inst['filename'] = $info->getFilename();
                            $inst['isdelete'] = 0;
                            $inst['create_time'] = time();
                            $result = Db::name('mk_importdata') ->insert($inst);
                            if ($result) {
                                array_shift($data);
                                //去掉第一行表头数据
                                $newdata = [];
                                foreach($data as $k=>$v) {
                                    foreach($v as $key=>$value) {
                                        $keys = \PHPExcel_Cell::stringFromColumnIndex($key);
                                        $add[$keys] = $value;
                                    }
                                    $newdata[] = $add;
                                }
                                //dump($newdata);die;
                                $result = Db::table($excdatatable) ->insertAll($newdata);
                                if (!$result) {
                                    Db::rollback();
                                }
                            } else {
                                Db::rollback();
                            }
                            //dump($result);die;
                        } else {
                            Db::rollback();
                        }
                        Db::commit();
                        unlink($path);
                        //删除文件
                    }
                    $end = microtime(true); //开启代码运行时间
                    $msg = '上传成功：耗时'.round($end-$start, 3) .'秒'."\n";
                    $msg.= 'use memory:'.memory_get_usage() ."\n";
                    return msg('1', $path, $msg);
                } else {
                    return msg('-1', '', $file->getError());
                }
            }
        }
        public function abcd($data) {
            foreach($data as $key=>$value) {
                $keys = \PHPExcel_Cell::stringFromColumnIndex($key);
                yield$add[$keys] = $value;
            }
        }
        /**
        * 下载导入与模板
        * @author: pan
        * @Date: times
        */
        public function downExcelTemplate() {
            require_once (EXTEND_PATH.'/PHPExcel/Classes/PHPExcel.php');
            $param = request() ->param();
            $columnslist = json_decode($param['data'], true);
            $tablename = $param['tablename'];
            $database = config('database.database');    //数据库库名
            $prefix = config('database.prefix');    //数据库表扩展名
            $tablename = $prefix.$tablename;
            $sql = "select COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE from information_schema.columns where table_schema = '$database' and table_name = '$tablename' ";
            $tableinnfo = Db::query($sql);
            $objPHPExcel = new \PHPExcel();
            $n = 0;
            //设置表头
            foreach($tableinnfo as $key=>$val) {
                //            $name = trim($val['COLUMN_COMMENT']);
                //            if($val['COLUMN_NAME']=='id'){
                //                $name = '序号';
                //            }
                if (array_key_exists($val['COLUMN_NAME'], $columnslist)) {
                    $name = $columnslist[$val['COLUMN_NAME']];
                    $beginCol = \PHPExcel_Cell::stringFromColumnIndex($n) .(1);
                    $objPHPExcel->getActiveSheet() ->setCellValue($beginCol, $name);
                    $n++;
                }
            }
            $fileName = iconv("utf-8", "gb2312", '模板表.xlsx');
            //重命名表
            ob_end_clean();        //清除缓冲区,避免乱码
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$fileName.'"');
            header('Cache-Control: max-age=0');
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->save('php://output');
            exit;
        }
        /**
        * 有表导入 excel
        */
        public function tableImoprt() {
            require_once (EXTEND_PATH.'/PHPExcel/Classes/PHPExcel.php');
            setlocale(LC_ALL, 'zh_CN.UTF-8'); //解决 pathinfo 中文乱码的问题
            $start = microtime(true); //开启代码运行时间
            $extlist = 'xlsx,xls,csv';
            if (request() ->isAjax()) {
                $file = request() ->file('file');
                $param = request() ->param();
                if ($param) {
                    if (!empty($param['tablename'])) {
                        $tablename = $param['tablename'];
                    } else {
                        return msg('-1', '', '导入失败：表不存在');
                    }
                } else {
                    return msg('-1', '', '导入失败');
                }
                $oldFileinfo = $file->getInfo();
                $oldFileNameinfo = $oldFileinfo['name'];
                $fileinfo = pathinfo($oldFileNameinfo);
                $fileExt = $fileinfo['extension'];   //文件扩展
                $basename = $fileinfo['basename'];   //带扩展的文件名
                $oldfilename = $fileinfo['filename']; //不带扩展文件名
                $info = $file->validate(['ext'=>$extlist]) ->move(ROOT_PATH.'public'.DS.'upload'.'/'.'excle');
                if ($info) {
                    $path = './upload'.'/'.'excle'.'/'.date('Ymd') .'/'.$info->getFilename(); //文件保存路径
                    if (empty($path) OR !file_exists($path)) {
                        die ('file not exists!');
                    }
                    if ($fileExt == 'xlsx') {
                        $type = 'Excel2007';
                    } elseif ($fileExt == 'xls') {
                        $type = 'Excel5';
                    } else {
                        $type = 'CSV';
                    }
                    //echo $fileExt;die;
                    $objReader = \PHPExcel_IOFactory::createReader($type); //判断使用哪种格式
                    if (!$objReader->canRead($path)) {                //判断是否是其他文档修改后缀的excel
                        die ('No Excel!');
                    }
                    $objReader->setReadDataOnly(true); //只读取数据,会智能忽略所有空白行,这点很重要.
                    if ($fileExt == 'xlsx' || $fileExt == 'xls') {
                        $objPHPExcel = $objReader->load($path);
                        $data = $objPHPExcel->getSheet() ->toArray();
                    } elseif ($fileExt == 'csv') {
                        $objReader = $objReader->setDelimiter(',') ->setInputEncoding('GBK') ->setEnclosure('"') ->setLineEnding("\r\n") ->setSheetIndex(0);
                        $objPHPExcel = $objReader->load($path);
                        $data = $objPHPExcel->getSheet() ->toArray();
                    }
                    //array_shift($data);//去掉第一行表头数据
                    if ($data) {
                        $database = config('database.database');    //数据库库名
                        $prefix = config('database.prefix');    //数据库表扩展名
                        $tablename = $prefix.$tablename;
                        $sql = "select COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE from information_schema.columns where table_schema = '$database' and table_name = '$tablename' ";
                        $tableinnfo = Db::query($sql);
                        foreach($tableinnfo as $key=>$val) {
                            //$fieldarrp[] = trim($val['COLUMN_NAME']);
                            if (trim($val['COLUMN_NAME']) != 'id' && trim($val['COLUMN_NAME']) != 'create_time' && trim($val['COLUMN_NAME']) != 'update_time' && trim($val['COLUMN_NAME']) != 'isdelete') {
                                $fieldarrp[] = trim($val['COLUMN_NAME']);
                            }
                        }
                        $header = $data[0];
                        $Colname = [];
                        $n = 0;
                        foreach($header as $k=>$vs) {
                            //判断每一列是否是时间类型的,时间类型，下面需要进行转换
                            if (strpos($vs, '时间') || strpos($vs, '日期')) {
                                $Colname[] = \PHPExcel_Cell::stringFromColumnIndex($k); //$K 转换成 0->A
                                $n++;
                            }
                        }
                        $columnslist = json_decode($param['data'], true);
                        Db::startTrans();
                        if ($tableinnfo) {
                            array_shift($data);
                            //去掉第一行表头数据
                            $newdata = [];
                            $field = explode(',', $param['field']);
                            $title = explode(',', $param['title']);
                            foreach($data as $k=>$v) {
                                foreach($header as $key=>$value) {
                                    if ($value != '序号') {
                                        $keys = \PHPExcel_Cell::stringFromColumnIndex($key);
                                        $fieledkey = array_keys($columnslist, $value) [0]; //返回键,也就是对应的数据表字段
                                        $fieldlist = array_column($tableinnfo, 'COLUMN_NAME'); //返回数组中指定的一列
                                        $tablfieledkey = array_keys($fieldlist, $fieledkey); //返回键
                                        $fieldtype = $tableinnfo[$tablfieledkey[0]]['DATA_TYPE']; //返回字段类型
                                        $add['isdelete'] = 0;
                                        if (is_float($v[$key])) {
                                            //判断是否是浮点类型
                                            if ($Colname) {
                                                if (in_array($keys, $Colname)) {
                                                    //PHPexxcel 读取时间长度会变成浮点类型，所以这里需要进行转换
                                                    $data = $v[$key];                                                //从excel导入后的时间
                                                    $d = 25569; //excel和php之间相差的时间
                                                    $t = 24*60*60; //一天24小时 echo gmdate('Y-m-d H:i:s', (data - d) *t);
                                                    //时间类型判断
                                                    if (gmdate('Y', ($data-$d) *$t) == '1970') {
                                                        $add[$fieledkey] = null;
                                                    } else {
                                                        if ($fieldtype == 'datetime') {
                                                            $add[$fieledkey] = gmdate('Y-m-d H:i:s', ($data-$d) *$t);
                                                        }
                                                        if ($fieldtype == 'date') {
                                                            $add[$fieledkey] = gmdate('Y-m-d', ($data-$d) *$t);
                                                        }
                                                        if ($fieldtype == 'time') {
                                                            $add[$fieledkey] = gmdate('H:i:s', ($data-$d) *$t);
                                                        }
                                                        if ($fieldtype == 'timestamp') {
                                                            $add[$fieledkey] = gmdate('Ymd His', ($data-$d) *$t);
                                                        }
                                                        if ($fieldtype == 'year') {
                                                            $add[$fieledkey] = gmdate('Y', ($data-$d) *$t);
                                                        }
                                                        if ($fieldtype == 'int') {
                                                            $add[$fieledkey] = strtotime(gmdate('Y-m-d H:i:s', ($data-$d) *$t+1));
                                                        }
                                                    }
                                                } else {
                                                    $add[$fieledkey] = $v[$key];
                                                }
                                            } else {
                                                $add[$fieledkey] = $v[$key];
                                            }
                                        } else {
                                            $add[$fieledkey] = $v[$key];
                                        }
                                    }
                                }
                                $newdata[] = $add;
                            }
                            //dump($newdata);die;
                            $result = Db::table($tablename) ->insertAll($newdata);
                            if (!$result) {
                                $msg = "导入失败";
                                $code = -1;
                                Db::rollback();
                            } else {
                                $msg = "导入成功";
                                $code = 1;
                            }
                        } else {
                            $msg = "导入失败";
                            $code = -1;
                            Db::rollback();
                        }
                        Db::commit();
                        unlink($path);
                        //删除文件
                    } else {
                        $msg = "导入失败:没有数据";
                        $code = -1;
                    }
                    $end = microtime(true); //开启代码运行时间
                    //                $msg  = '耗时' . round($end - $start, 3) . '秒' . "\n";
                    //                $msg  .= 'use memory:' . memory_get_usage() . "\n";
                    return msg($code, $path, $msg);
                } else {
                    return msg('-1', '', $file->getError());
                }
            }
        }
    }
    